/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit_vendor_management;

import java.sql.*;
import java.text.DecimalFormat;

/**
 *
 * @author QuangKhoi
 */
public class Database
{
    public static Database instance = new Database();

    //Primary Connection to use since the database object is created
    private Connection PrimaryConnection;

    //Exchange
    private static String[][] DBCode = new String[][]{{"MaDaiLy","D"},
                                            {"MaMatHang","M"},
                                            {"MaChiTietPhieuXuat","C"},
                                            {"MaLoaiDaiLy","L"},
                                            {"MaPhieuThu","T"},
                                            {"SoPhieuXuat","X"},
                                            {"MaQuan","Q"},
                                            {"ID","Z"}};

    /// <summary>
    /// Create custom connection String and open the connection
    /// </summary>
    /// <param name="Host">Host name of the server containing database</param>
    /// <param name="Username">Username provided to access the database (with neccessary permissions)</param>
    /// <param name="Password">Password provided</param>
    /// <param name="Database">Database name in the database server</param>
    private Database(String Host, String Username, String Password, String Database) {
        //Create Connection String
        String ConnectionString = "User Id=" + Username + ";Password=" + Password +
            ";Server=" + Host + ";Database=" + Database;
        //Init a connection
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://locahost;databaseName=uit_vendormanagement;user=sa;password=admin10520462;";
            PrimaryConnection = DriverManager.getConnection(connectionUrl);
        }
        catch(Exception e) {
            System.out.println("Cound not connect to SQL Database");
            System.out.println(e.getMessage());
        }
    }

    /// <summary>
    /// Constructor create the connection and open it (with default Connection String)
    /// </summary>
    private Database() {
        //Create default Connection String
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://ITACHI\\SQLEXPRESS;databaseName=uit_vendormanagement;user=itachi0605;password=thipham;";
            PrimaryConnection = DriverManager.getConnection(connectionUrl);
        }
        catch(Exception e) {
            System.out.println("Cound not connect to SQL Database");
            System.out.println(e.getMessage());
        }
    }

    /// <summary>
    /// Receice the SQL String and do the query
    /// </summary>
    /// <param name="SQL">Query String</param>
    /// <returns>QueryResult (include Error Code & Error Mefssage)</returns>
    public QueryResult Query(String SQL) {
        //QueryResult object to store the result of the SQL
        QueryResult result = new QueryResult();
        ResultSet rs;
        
        //First check if they have query input
        if (!"".endsWith(SQL)) {

            //SELECT is a retrieve-SQL and then put the data into DataTable
            if ("SELECT".endsWith(SQL.substring(0, 6))) {
                try {
                    Statement stmt = 
                            PrimaryConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                     ResultSet.CONCUR_READ_ONLY);
                    rs = stmt.executeQuery(SQL);                    
                    //Fill data to Table and then return OK code (200)
                    SQLWarning wn = rs.getWarnings();
                    if (wn == null) {
                        result.ReturnCode = 200;
                        ResultSetMetaData metaData = rs.getMetaData();
                        int totalColumn = metaData.getColumnCount();
                        DataTable table = new DataTable();
                        Object[] dataRow = new Object[totalColumn];
                        if(rs!= null)
                        {
                            for(int i=1;i<=totalColumn;i++)
                                table.addColumn(metaData.getColumnName(i));
                            while(rs.next())
                            {
                                result.Count++;
                                for(int i=1;i<=totalColumn;i++)
                                    dataRow[i-1] = rs.getObject(i);
                                table.addRow(dataRow);
                            }

                        }
                        result.Table = table;
                    }
                    else {
                        result.ReturnCode = wn.getErrorCode();
                        result.Error = wn.getMessage();
                    }
                }
                catch (Exception err) {
                    //There's some mistakes with the query or the database or the data exist in the database
                    //then return the Conflict code with error message from the catched Exception
                    result.Error = err.getMessage();
                    result.ReturnCode = 409;
                }

            }
            else
            {
                try
                {
                    Statement stmt = PrimaryConnection.createStatement();
                    //Execute the query (non-data) and then return OK code
                    rs = stmt.executeQuery(SQL);
                    SQLWarning wn = rs.getWarnings();
                    if (wn == null) {
                        result.ReturnCode = 200;
                    }
                    else {
                        result.ReturnCode = wn.getErrorCode();
                        result.Error = wn.getMessage();
                    }
                }
                catch (Exception err) {
                    //Return conflict code
                    result.Error = err.getMessage();
                    result.ReturnCode = 409;
                }
            }
        }
        return result;
    }

    /// <summary>
    /// Check the duplicate value of the table
    /// </summary>
    /// <param name="Table">The Table you want to check</param>
    /// <param name="Column">The Column you want to check</param>
    /// <param name="Value">The Value you want to check</param>
    /// <returns></returns>
    public static Boolean isDupplicate(String Table, String Column, String Value) 
    {
        QueryResult result = new QueryResult();
        try {
            //Check the duplicate value (OK)
            result = instance.Query("SELECT COUNT(*) FROM " + Table + " WHERE " +
                Column + " = '" + Value + "'");
            if (result.ReturnCode == 200)
                if (result.Count > 0)
                    return true;
        }
        catch (Exception err) {
            //There's some mistakes with the user input value or database
            result.ReturnCode = 409;
            result.Error = err.getMessage();
        }
        return false;
    }


    /// <summary>
    /// Get the ID in order to insert a new row
    /// </summary>
    /// <param name="Table">Table name</param>
    /// <param name="Column">Column name (contains ID)</param>
    /// <returns></returns>
    public static String getID(String Table, String Column) {
        QueryResult result = new QueryResult();
        try {
            //Check the duplicate value (OK)
            result = instance.Query("SELECT Max(" + Column + ") FROM " + Table);
            result.ReturnCode = 200;
            if (result.Count > 0 
                    && !result.Table.getString(0, 0).equals("")) {
                String sNumber = result.Table.getString(0, 0);
                Integer Number = Integer.parseInt(sNumber.substring(1));
                return formatID(Column, Number + 1);
            }
            else
                return formatID(Column, 1);
        }
        catch (Exception err)
        {
            //There's some mistakes with the user input value or database
            result.ReturnCode = 409;
            result.Error = err.getMessage();
        }
        return null;
    }

    /// <summary>
    /// Format number to String
    /// </summary>
    /// <param name="Column">Column name</param>
    /// <param name="Value">Value of number</param>
    /// <returns></returns>
    public static String formatID(String Column, int Value)
    {
        for (int i = 0; i < DBCode.length / 2; i++)
            if (DBCode[i][0].endsWith(Column)) {
                DecimalFormat myFormatter = new DecimalFormat("000000000");
                return DBCode[i][1] + myFormatter.format(Value);
            }
        return null;
    }

    /// <summary>
    /// Convert DateTime to SQL format
    /// </summary>
    /// <param name="_DateTime">DateTime input</param>
    /// <returns></returns>
    public static java.sql.Date sqlDateTime(Date _DateTime) {
        return new java.sql.Date(_DateTime.getTime());
    }
}